package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import ModelLayer.Location;

public class DbLocation implements IFDBLocation  {
	private Connection con;
	
	public DbLocation(){
		con = DbConnection.getInstance().getDBcon();
	}
	
	@Override
	public Location getCityByCodeZip(String cityZip, String countryZip) {
		String wClause = "cityzip = '" + cityZip + "' AND countryZip = '" + countryZip + "'";
		System.out.println("Search location " + wClause);
		return singleWhere(wClause);
	}
	
	@Override
	public Location getLocationById(int locationId) {
		String wClause = "id = '" + locationId + "'";
		System.out.println("Search location " + wClause);
		return singleWhere(wClause);
	}
	
	//Singlewhere to select a city 	
		private Location singleWhere(String wClause)
		{
			ResultSet results;
			Location locationObj = new Location();
			
		
				String query =  buildQuery(wClause);
				System.out.println(query);
				try{ // read the customer from the database
					Statement stmt = con.createStatement();
					stmt.setQueryTimeout(5);
					results = stmt.executeQuery(query);

					if( results.next() ){
						locationObj = buildLocation(results);
						stmt.close();
					}		
					else{ //no customer was found
						locationObj = null;
					}
				}
				catch(Exception e){
					System.out.println("Query exception: "+e);
				}

			return locationObj;
		}
		
		//method to build the query for Customer
		private String buildQuery(String wClause)
		{
			String query="SELECT id, countryZip, cityZip, city, country FROM Location";

			if (wClause.length()>0)
				query=query+" WHERE "+ wClause;

			return query;
		}
		
		//method to build a PrivateCustomer object
		private Location buildLocation(ResultSet results)
		{   
			Location locationObj = new Location();
			try{ // the columns from the table customer  are used
				locationObj.setId(results.getInt("id"));
				locationObj.setCity(results.getString("city"));
				locationObj.setCityZip(results.getString("cityZip"));
				locationObj.setCountry(results.getString("country"));
				locationObj.setCountryZip(results.getString("countryZip"));
	
			}
			catch(Exception e)
			{
				System.out.println("error in building the city object");
			}
			return locationObj;
		}
}
